package com.epam.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.dao.intf.IUserDAO;
import com.epam.database.ConnectionPool;
import com.epam.entity.AbstractEntity;
import com.epam.entity.User;

public class UserDAO implements IUserDAO {
	
	private static final Logger LOG = Logger.getLogger(UserDAO.class);
	private static final String INSERT_USER = "INSERT INTO USERS (login, password, role) values (?,?,?)";
	private static final String FIND_USER_BY_ID = "SELECT * FROM users WHERE user_id = ?";
	private static final String FIND_USER_BY_LOGIN = "SELECT * FROM users WHERE login = ?";
	private static final String UPDATE_USER = "UPDATE users SET password = ?, role = ? WHERE login = ?";
	private static final String FIND_ALL_USERS = "SELECT * FROM users";
	
	@Override
	public void insert(AbstractEntity entity) {
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(INSERT_USER);
			
			User user = (User) entity;
			st.setString(1, user.getLogin());
			st.setString(2, user.getPassword());
			st.setString(3, user.getRole().toString());
			LOG.info("User inserted " + user.toString());
			st.execute();
			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}

	}

	@Override
	public List<AbstractEntity> findAll() {
		List<AbstractEntity> users = null;;
		try{
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection.prepareStatement(FIND_ALL_USERS);
			
			ResultSet rs = st.executeQuery();
			users = new LinkedList<AbstractEntity>();
			
			while (rs.next()) {
			
				User user = construct(rs); 
				users.add(user);
			}
			ConnectionPool.INSTANCE.retreiveConnection(connection);
			
		} catch (SQLException e) {
			LOG.error(e);
		}
		return users;
	}

	@Override
	public User findByID(int id) {
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_USER_BY_ID);
			
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();

			rs.next();
			
			User user = construct(rs);

			ConnectionPool.INSTANCE.retreiveConnection(connection);
			return user;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			LOG.error(e);
		}
		return null;
	}

	@Override
	public User findByLogin(String login) {

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_USER_BY_LOGIN);
			st.setString(1, login);
			ResultSet rs = st.executeQuery();

			if (rs.isBeforeFirst()) {
				rs.next();
				User user = construct(rs);
				
				ConnectionPool.INSTANCE.retreiveConnection(connection);
				return user;

			}
		} catch (SQLException e) {
			LOG.error(e);
		}
		return null;

	}

	@Override
	public void update(User user) {
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(UPDATE_USER);
			
			st.setString(1, user.getPassword());
			st.setString(2, user.getRole().toString());
			st.setString(3, user.getLogin());
			st.execute();

			ConnectionPool.INSTANCE.retreiveConnection(connection);
		} catch (SQLException e) {
			LOG.error(e);
		}
	}

	private User construct(ResultSet rs) {
		User user = null;
		try {
		user = new User();
		user.setId(Integer.parseInt(rs.getString("user_id")));
		user.setLogin(rs.getString("login"));
		user.setPassword(rs.getString("password"));
		user.setRole(Enum.valueOf(User.Role.class, rs.getString("role")));
		
		
		
		} catch (SQLException e) {
			LOG.error(e);
		}
		return user; 
	}

	@Override
	public List<AbstractEntity> findAllPaginated(int offset, int numberOfRecords) {
		Connection connection = ConnectionPool.INSTANCE.takeConnection();
		List<AbstractEntity> users = new ArrayList<AbstractEntity>();
				
		try {
			PreparedStatement st = connection.prepareStatement("SELECT * FROM users OFFSET ? LIMIT ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, offset);
			st.setInt(2, numberOfRecords);
			ResultSet rs = st.executeQuery();
			
			while(rs.next()) {
				User user = construct(rs);
				users.add(user);
			}
			
		} catch (SQLException e) {
			LOG.error(e);
		}
		ConnectionPool.INSTANCE.retreiveConnection(connection);
		return users;
		
	}
	
}
